using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DeleteInput
/// </summary>
public class DeleteInput
{
    public DeleteInput()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    LoadEditPage LE = new LoadEditPage();
    public ArrayList DeleteOperation(DataSet dsePageInfo, string strKeys)
    {
        string strPageId = "";
        ArrayList arlCmdObj = new ArrayList();
        ArrayList arlPageCmdObj = new ArrayList();
        DataTable dtaPageSet = dsePageInfo.Tables["PageSet"];
        DataTable dtaPage = dsePageInfo.Tables["Page"];
        DataTable dtaPageKeys = dsePageInfo.Tables["PageKeys"];
        DataTable dtaPageCtrl = dsePageInfo.Tables["PageCtrl"];
        security objSec = new security();
        string strPageKeyValues = "";
        string[] strPageKeys = strKeys.Split(GlobalValues.PageSetKeyChar);
        int idtPageRowsCount = dtaPage.Rows.Count;
        for (int i = 0; i < idtPageRowsCount; i++)
        {
            if (!Convert.ToBoolean(objSec.getObjectRightsForUser(dtaPage.Rows[i]["object_id"].ToString(), "delete")))
                continue;
            strPageId = dtaPage.Rows[i]["page_id"].ToString();
            if (i < strPageKeys.Length)
            {
                strPageKeyValues = strPageKeys[i];
            }
            else
            {
                strPageKeyValues = LE.GetPageKey(dtaPageKeys, dtaPage, i, strKeys);
            }
            arlPageCmdObj = DeletePage(dtaPage, dtaPageCtrl.Select("page_id='" + strPageId.Replace("'", "''") + "'"), strPageKeyValues, dtaPageKeys, i, dsePageInfo);
            for (int j = 0; j < arlPageCmdObj.Count; j++)
            {
                arlCmdObj.Add(arlPageCmdObj[j]);
            }
        }
        //To avoid the foreign key constriant...Command object order needs to be reversed
        ArrayList arlRetCmdObj = new ArrayList();
        for (int i = arlCmdObj.Count - 1; i >= 0; i--)
        {
            arlRetCmdObj.Add(arlCmdObj[i]);
        }

        DeleteFromNotes(ref arlRetCmdObj, dsePageInfo, strKeys, strPageKeys); // Added by srivatsan for notes delete when master is deleted
        return arlRetCmdObj;
    }

    // Added by srivatsan on Sep 29 2009 for Notes Changes
    // This function will create query to delete the notes records for the corresponding master record been deleted
    private void DeleteFromNotes(ref ArrayList arlRetCmdObj,DataSet dsPageInfo,string strKeys,string[] strPageKeys)
    {
        SqlCommand cmd = null;
        try
        {
            if (strKeys.Trim() != "")
            {
                if (dsPageInfo != null)
                {
                    if (dsPageInfo.Tables.Count > 0)
                    {
                        if (dsPageInfo.Tables["ObjectNotes"] != null)
                        {
                            if (dsPageInfo.Tables["ObjectNotes"].Rows.Count > 0)
                            {
                                string associate_notes = dsPageInfo.Tables["ObjectNotes"].Rows[0]["associate_notes"].ToString();

                                if (associate_notes.ToLower().Trim() != "")
                                {
                                    if (Convert.ToBoolean(associate_notes.ToLower().Trim()))
                                    {
                                        string deletekeys = "";
                                        for (int i = 0; i < strPageKeys.Length; i++)
                                        {
                                            if (strPageKeys[i].ToLower().Trim() != "")
                                            {
                                                deletekeys = deletekeys + "'" + strPageKeys[i].Trim() + "',";
                                            }
                                        }
                                        if (deletekeys.Trim() != "")
                                        {
                                            deletekeys = deletekeys.Substring(0, deletekeys.LastIndexOf(","));
                                        }

                                        if (deletekeys.Trim() != "")
                                        {
                                            cmd = new SqlCommand("delete from sy_notes where related_af_row_id in (" + deletekeys + ")");

                                            arlRetCmdObj.Add(cmd);
                                        }
                                    }

                                }
                            }
                        }
                    }

                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : DeleteFromNotes :" + ex.Message);
        }
        finally
        {
            cmd = null;
        }

    }

    private void getChildCommand(ref ArrayList cmdArrayList, DataRow drChild, string KeyValue, HDDatabase HDD)
    {
        string sSql = "select * from sy_obj where af_row_id='" + drChild["object_id"].ToString() + "'";
        DataRow dr = HDD.GetTableRow(sSql);
        sSql = "delete from [" + dr["object_table"].ToString().Replace("'", "''") + "] where [" + dr["object_table"].ToString().Replace("'", "''") + "].[" + drChild["attrib_id"].ToString().Replace("'", "''") + "]='" + KeyValue + "'";
        SqlCommand cmdObj = new SqlCommand(sSql);
        cmdArrayList.Add(cmdObj);
    }

    private void getChildObjects(ref DataTable dtChild, HDDatabase HDD, ref ArrayList cmdArrayList, string KeyValue)
    {
        foreach (DataRow dr in dtChild.Rows)
        {
            string sSql = "select * from sy_obj where af_row_id='" + dr["object_id"].ToString() + "'";
            DataRow drNew = HDD.GetTableRow(sSql);
            sSql = "select * from sy_obj_mas_detail where master_obj='" + drNew["object_id"].ToString() + "'";
            DataTable dt = HDD.GetDataTable(sSql);
            sSql = "select af_row_id from [" + drNew["object_table"].ToString() + "] where [" + dr["attrib_id"].ToString() + "]='" + KeyValue + "'";
            DataTable dtData = HDD.GetDataTable(sSql);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow drChild in dt.Rows)
                {
                    sSql = "select * from sy_obj where af_row_id='" + drChild["object_id"].ToString() + "'";
                    drNew = HDD.GetTableRow(sSql);

                    foreach (DataRow drData in dtData.Rows)
                    {
                        getChildObjects(ref dt, HDD, ref cmdArrayList, drData["af_row_id"].ToString());
                        sSql = "delete from [" + drNew["object_table"].ToString() + "] where [" + drChild["attrib_id"].ToString() + "] ='" + drData["af_row_id"].ToString() + "'";
                        SqlCommand cmdObj = new SqlCommand(sSql);
                        cmdArrayList.Add(cmdObj);
                    }

                }
            }
            dtData.Dispose();
            dtData = null;
            dt.Dispose();
            dt = null;
            drNew = null;
        }
    }

    public bool DeleteChildRecords(ref ArrayList cmdArrayList, DataTable PageInfo, string KeyValue, HDDatabase HDD)
    {
        string sObject_id = PageInfo.Rows[0]["object_id"].ToString();
        string sSQL = "select * from sy_obj_mas_detail where master_obj='" + sObject_id + "'";
        DataTable dt = HDD.GetDataTable(sSQL);

        #region Added by 'Imran Gani' on 08-May-2013, this code is only to delete the page designer related records
        if (HttpContext.Current.Request.QueryString["pgs_id"].ToString() == "ps_pageset")
            DeletePageRelatedRecords(ref cmdArrayList, KeyValue, HDD);
        #endregion

        getChildObjects(ref dt, HDD, ref cmdArrayList, KeyValue);

        foreach (DataRow dr in dt.Rows)
            getChildCommand(ref cmdArrayList, dr, KeyValue, HDD);
        dt.Dispose();
        dt = null;

        return true;
    }

    #region Added by 'Imran Gani' on 08-May-2013, this code is only to delete the page designer related records
    /// <summary>
    /// To delete the page related recodes from page designer list delete
    /// </summary>
    /// <param name="cmdArrayList">Array to carry queries.</param>
    /// <param name="KeyValue">Key value of pageset_id</param>
    /// <param name="HDD">HDDatabase</param>
    // Added by 'Imran Gani' on 08-May-2013, to delete only the page related records.
    private void DeletePageRelatedRecords(ref ArrayList cmdArrayList, string KeyValue, HDDatabase HDD)
    {
        try
        {
            DataRow dr = HDD.GetTableRow("select * from sy_pgs_mem where pageset_id = '" + KeyValue + "'");

            // To replace the table name for not lossing data.
            DataRow drObj = HDD.GetTableRow("select * from [sy_obj] where [object_id] ='" + dr["object_id"].ToString() + "';");
            SqlCommand cmdObj = new SqlCommand("sp_rename [" + drObj["object_table"].ToString() + "], [" + drObj["object_table"].ToString() + "_af_deleted_" + drObj["af_row_id"].ToString() + "];");
            cmdArrayList.Add(cmdObj);

            string sSql = "delete from [sy_pg_ctr] where page_id in (select af_row_id from sy_pg where page_id= '" + dr["page_id"].ToString() + "');";
            sSql += "delete from [sy_pg_evnt] where page_id in (select af_row_id from sy_pg where page_id= '" + dr["page_id"].ToString() + "');";
            sSql += "delete from [sy_pg_steps] where page_id in (select af_row_id from sy_pg where page_id= '" + dr["page_id"].ToString() + "');";
            sSql += "delete from [sy_pg_ctr_evnt] where page_id in (select af_row_id from sy_pg where page_id= '" + dr["page_id"].ToString() + "');";
            sSql += "delete from [sy_pg_rel_info] where page_id in (select af_row_id from sy_pg where page_id= '" + dr["page_id"].ToString() + "');";
            sSql += "delete from [sy_pg] where [page_id] ='" + dr["page_id"].ToString() + "';";
            
            sSql += "delete from [sy_obj_att] where [object_id] in (select af_row_id from sy_obj where object_id = '" + dr["object_id"].ToString() + "');";
            sSql += "delete from [sy_obj_mas_detail] where [object_id] in (select af_row_id from sy_obj where object_id = '" + dr["object_id"].ToString() + "');";
            sSql += "delete from [sy_obj] where [object_id] ='" + dr["object_id"].ToString() + "';";
            sSql += "delete from [sy_obj_acc] where [object_id] ='" + dr["object_id"].ToString() + "';";

            SqlCommand cmdPageDetails = new SqlCommand(sSql);
            cmdArrayList.Add(cmdPageDetails);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : DeletePageRelatedRecords :" + ex.Message);
        }
    }
    #endregion

    public ArrayList DeletePage(DataTable dtaPage, DataRow[] droPageCtrl, string strPageKeyValues, DataTable dtaPageKeys, int intPageSeq)
    {
        ArrayList arlReturn = new ArrayList();
        string strSql = "";
        string strWhrCond = "";
        DataRow droPage = dtaPage.Rows[intPageSeq];
        string strTableName = droPage["page_table_name"].ToString();
        HDDatabase HDD = new HDDatabase();
        SqlCommand cmdObj;

        cmdObj = new SqlCommand();
        strWhrCond = GetWhereCond(dtaPage, dtaPageKeys, droPageCtrl, strPageKeyValues, intPageSeq);
        strSql = "Delete from [" + strTableName + "] where " + strWhrCond;
        cmdObj.CommandText = strSql;
        arlReturn.Add(cmdObj);
        //if (HttpContext.Current.Request.QueryString["DPK"] != null)  // Commented by srivatsan for delete issue
        //{
        //    DeleteChildRecords(ref arlReturn, dtaPage, strPageKeyValues, HDD);
        //    //DeleteRelationRecords(ref arlReturn, droPage, HttpContext.Current.Request.QueryString["DPK"], ref HDD);
        //}
        if (HttpContext.Current.Session["DPK"] != null)
        {
            DeleteChildRecords(ref arlReturn, dtaPage, strPageKeyValues, HDD);
        }
        HDD = null;
        return arlReturn;
    }

    public ArrayList DeletePage(DataTable dtaPage, DataRow[] droPageCtrl, string strPageKeyValues, DataTable dtaPageKeys, int intPageSeq, DataSet dsPageInfo)
    {
        ArrayList arlReturn = new ArrayList();
        string strSql = "";
        string strWhrCond = "";
        DataRow droPage = dtaPage.Rows[intPageSeq];
        string strTableName = droPage["page_table_name"].ToString();
        HDDatabase HDD = new HDDatabase();
        SqlCommand cmdObj;

        cmdObj = new SqlCommand();
        strWhrCond = GetWhereCond(dtaPage, dtaPageKeys, droPageCtrl, strPageKeyValues, intPageSeq);
        strSql = "Delete from [" + strTableName + "] where " + strWhrCond;
        cmdObj.CommandText = strSql;
        arlReturn.Add(cmdObj);
        ////if (HttpContext.Current.Request.QueryString["DPK"] != null) // Commented by srivatsan for delete issue
        ////{
        ////    DeleteChildRecords(ref arlReturn, dtaPage, strPageKeyValues, HDD);
        ////    DeleteRelationRecords(ref arlReturn, droPage, HttpContext.Current.Request.QueryString["DPK"], ref HDD, dsPageInfo);
        ////}

        if (HttpContext.Current.Session["DPK"] != null)
        {
            DeleteChildRecords(ref arlReturn, dtaPage, strPageKeyValues, HDD);
            //DeleteRelationRecords(ref arlReturn, droPage, HttpContext.Current.Session["DPK"].ToString(), ref HDD, dsPageInfo);
            RelationOperation objRO = new RelationOperation();
            objRO.ApplyRelationOperation(ref arlReturn, HttpContext.Current.Session["DPK"].ToString(), dtaPage, null, "DELETE");
            objRO = null;
        }
        HDD = null;
        return arlReturn;
    }

    public ArrayList DeleteMultipleRecords(DataTable dtaPage, DataRow[] droPageCtrl, string strPageKeyValues, DataTable dtaPageKeys, int intPageSeq, ref DataTable dtInput)
    {
        ArrayList arlReturn = new ArrayList();
        string strSql = "";
        string strWhrCond = "";
        DataRow droPage = dtaPage.Rows[intPageSeq];
        string strTableName = droPage["page_table_name"].ToString();
        SqlCommand cmdObj;
        foreach (DataRow dr in dtInput.Rows)
        {
            cmdObj = new SqlCommand();
            strWhrCond = "af_row_id='" + dr["af_row_id"].ToString() + "'";//GetWhereCond(dtaPage, dtaPageKeys, droPageCtrl, strPageKeyValues, intPageSeq);
            strSql = "Delete from [" + strTableName + "] where " + strWhrCond;
            cmdObj.CommandText = strSql;
            arlReturn.Add(cmdObj);
        }

        return arlReturn;
    }

    private string GetWhereCond(DataTable dtaPage, DataTable dtaPageKeys, DataRow[] droPageCtrl, string strPageKeyValues, int intPageSeq)
    {
        string strWhCond = "";
        DataRow droPage = dtaPage.Rows[intPageSeq];
        string strPageId = droPage["page_id"].ToString();
        string sAF_ROW_ID = droPage["af_row_id"].ToString();
        string strPageTblName = droPage["page_table_name"].ToString();
        //DataRow[] droPageKeys = dtaPageKeys.Select("page_id='" + sAF_ROW_ID + "'");//, "page_key_seq");
        DataRow[] droPageKeys = dtaPageKeys.Select("af_row_id='" + sAF_ROW_ID + "'");//, "page_key_seq");
        string strKeyCol = "";
        string[] strKeys = strPageKeyValues.Split('~');
        for (int i = 0; i < strKeys.Length; i++)
        {
            strKeyCol = "af_row_id"; //strKeyCol = droPageKeys[i]["page_key"].ToString();
            if (strWhCond != "")
            {
                strWhCond += " and ";
            }
            strWhCond += " [" + strPageTblName + "].[" + strKeyCol + "] = '" + strKeys[i] + "' ";
        }
        return strWhCond;
    }

    private void DeleteRelationRecords(ref ArrayList cmdArrayList, DataRow PageInfo, string KeyValue, ref HDDatabase HDD, DataSet dsPageInfo)
    {
        try
        {
            DataTable dtRelation = HDD.GetDataTable("select * from sy_relation where is_active='1' and operations like '%delete%'");
            PageInfoLoad PIL = new PageInfoLoad();
            DataRow[] drr = dtRelation.Select("source_table='" + PageInfo["page_id"].ToString() + "'");
            foreach (DataRow dr in drr)
            {
                DataSet dsPageInfoTemp = PIL.GetPageSetPageCtrlInfo(HDD.GetColumnValue("select sy_pgs.af_row_id from sy_pgs, sy_pgs_mem where sy_pgs.af_row_id=sy_pgs_mem.pageset_id and sy_pgs_mem.page_id='" + dr["Relation_Table"].ToString() + "'"));

                string sKeyValue = getKeyValueForRelationTable(PageInfo["page_table_name"].ToString().Replace("'", "''"), dr["Source_Field"].ToString(), "af_row_id", KeyValue, ref HDD);
                string sTableName = HDD.GetColumnValue("Select page_table_name from sy_pg where page_id='" + dr["Relation_Table"].ToString() + "'");
                getRelationCommand(ref cmdArrayList, sTableName, dr["Relation_Field"].ToString(), dr["condition"].ToString().Trim(), sKeyValue, ref HDD);
                String sSql = "select * from " + sTableName + " where " + dr["Relation_Field"].ToString() + "='" + sKeyValue + "' ";

                if (dr["condition"].ToString().Trim() != "")
                {
                    sSql = sSql + "and " + dr["condition"].ToString();
                }

                if (HDD.GetDataTable(sSql).Rows.Count > 0)
                {
                    if (dr["Relation_Field"].ToString() != "af_row_id")
                    {
                        sKeyValue = HDD.GetColumnValue("select af_row_id from " + sTableName + " where " + dr["Relation_Field"].ToString().Replace("'", "''") + "='" + sKeyValue.Replace("'", "''") + "'");
                    }

                    if (sKeyValue != "")
                    {
                        DeleteChildRecords(ref cmdArrayList, dsPageInfoTemp.Tables["Page"], sKeyValue, HDD);
                    }
                }
            }
            PIL = null;
            drr = null;
            dtRelation.Dispose();
            dtRelation = null;
        }

        catch
        {
        }

        finally
        {
        }
    }

    private String getKeyValueForRelationTable(String SourceTableName, String SouceFieldName, String KeyField, String KeyValue, ref HDDatabase HDD)
    {
        string sReturn = "";
        try
        {
            //datatype is to be determined for quote identifier
            sReturn = HDD.GetColumnValue("select [" + SouceFieldName.Replace("'", "''") + "] from [" + SourceTableName.Replace("'", "''") + "] where  [" + KeyField.Replace("'", "''") + "]='" + KeyValue.Replace("'", "''") + "'");
        }
        catch
        {
        }
        finally
        {
        }
        return sReturn;
    }

    private void getRelationCommand(ref ArrayList cmdArrayList, String RelationTable, String RelationField, String Condition, String KeyValue, ref HDDatabase HDD)
    {
        String sSql = "";

        if (Condition == "")
        {
            sSql = "delete from [" + RelationTable.Replace("'", "''") + "] where [" + RelationField.Replace("'", "''") + "]='" + KeyValue.Replace("'", "''") + "'";
        }
        else
        {
            sSql = "delete from [" + RelationTable.Replace("'", "''") + "] where [" + RelationField.Replace("'", "''") + "]='" + KeyValue.Replace("'", "''") + "' and " + Condition;
        }

        SqlCommand cmdObj = new SqlCommand(sSql);
        cmdArrayList.Add(cmdObj);
    }

    public SqlCommand DeleteRelationPage(DataTable dtaPage, String KeyField, String strPageKeyValues, DataTable dtaPageKeys, int intPageSeq)
    {
        string strSql = "";
        string strWhrCond = "";
        DataRow droPage = dtaPage.Rows[intPageSeq];
        string strTableName = droPage["page_table_name"].ToString();
        HDDatabase HDD = new HDDatabase();
        SqlCommand cmdObj = new SqlCommand();
        strWhrCond = GetWhereCondForRelation(dtaPage, KeyField, strPageKeyValues, intPageSeq);
        strSql = "Delete from [" + strTableName + "] where " + strWhrCond;
        cmdObj.CommandText = strSql;
        HDD = null;
        return cmdObj;
    }

    private string GetWhereCondForRelation(DataTable dtaPage, String KeyField, String KeyValue, int intPageSeq)
    {
        string strWhCond = "";
        DataRow droPage = dtaPage.Rows[intPageSeq];
        string strPageId = droPage["page_id"].ToString();
        string sAF_ROW_ID = droPage["af_row_id"].ToString();
        string strPageTblName = droPage["page_table_name"].ToString();

        strWhCond += " [" + strPageTblName + "].[" + KeyField + "] = '" + KeyValue + "' ";

        return strWhCond;
    }
}
